﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;

public partial class _35newpart_Linq_DLINQ_LINQ_to_SQL_之执行SQL语句的添加_查询_更新和删除 : System.Web.UI.Page
{

    Linq查询DataContext LDB = new Linq查询DataContext();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindData();
        }
    }
    /// <summary>
    /// 添加事伯
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        ///使用回调的函数

        LDB.ExecuteQuery<book>(@"insert into book (bookname,bookdate,bookid) values({0},{1},{2})"
            , txtCategoryName.Text, txtDescription.Text,LDB.books.OrderByDescending(p=>p.bookid).Take(1).First().bookid+1
            );


        BindData();

        ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert(\"ok添加成功\");", true);

    }

    /// <summary>
    /// 选定事伯
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvCategory_SelectedIndexChanged(object sender, EventArgs e)
    {
        // 使用NorthwindDataContext对象的ExecuteQuery<T>()方法，通过指定的SQL语句返回指定对象的集合

        DetailsView1.DataSource = LDB.ExecuteQuery<book>(@"select * from [book] where bookid={0}", gvCategory.SelectedValue);
        DetailsView1.DataBind();
    }

    /// <summary>
    /// 更新事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvCategory_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        LDB.ExecuteQuery<book>("update [book] set bookname={0} where bookid={1}", ((TextBox)gvCategory.Rows[e.RowIndex].Cells[1].Controls[0]).Text , gvCategory.DataKeys[e.RowIndex].Value);
        ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert('" + ((TextBox)gvCategory.Rows[e.RowIndex].Cells[1].Controls[0]).Text + "');", true);
        gvCategory.EditIndex = -1;
        BindData();
    }
    /// <summary>
    /// 点编辑热键的事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvCategory_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvCategory.EditIndex = e.NewEditIndex;

        BindData();
    }   
    /// <summary>
    /// 点删除的事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvCategory_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        //删除记录
        LDB.ExecuteQuery<book>(@"Exec DelBook {0}", gvCategory.DataKeys[e.RowIndex].Value.ToString());

        //var pp = LDB.books.Where(p => p.bookid == decimal.Parse(gvCategory.DataKeys[e.RowIndex].Value.ToString())).Single();
        //LDB.books.Remove(pp);
        //LDB.SubmitChanges();


        ScriptManager.RegisterStartupScript(UpdatePanel1, UpdatePanel1.GetType(), "", "alert('id=" +gvCategory.DataKeys[e.RowIndex].Value +"\\\r\\\n"+"已删除名为:"+gvCategory.Rows[e.RowIndex].Cells[1].Text+ "的东东');", true);
        BindData();
    }

    /// <summary>
    /// 取消事件
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvCategory_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvCategory.EditIndex = -1;
        BindData();
    }

    /// <summary>
    /// 帮定数据
    /// </summary>
    void BindData()
    {
        // 使用NorthwindDataContext对象的ExecuteQuery<T>()方法，通过指定的SQL语句返回指定对象的集合
        var datasource = LDB.ExecuteQuery<book>("select * from [book]").Select(p=>new {名=p.bookname,bookid=p.bookid});//执行SQL帮定数据源
        gvCategory.DataSource = datasource;
        gvCategory.DataBind();
    }



}
